SQL递归语句 |
您所在的位置:网站首页 › sql -4229 › SQL递归语句 |
SQL 递归语句
USE
[WPF] GO /****** Object: Table [dbo].[C_TREEVIEW] Script Date: 2019/10/7 12:48:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE
TABLE
[dbo] . [C_TREEVIEW] (
[GUID]
[nchar] ( 36 ) NULL ,
[ID]
[int]
NULL ,
[NAME]
[nvarchar] ( 100 ) NULL ,
[PID]
[int]
NULL ) ON
[PRIMARY] GO INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '05f2c76c-6b43-4577-b72d-94669def2958' , 0 , N ' 根节点 ' , - 1 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'a308d38b-8d96-45ff-b7d3-978ecacb1419' , 1 , N ' 爷爷 ' , 0 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '9b5f060a-ff40-4273-8c3d-a9c37056dcf5' , 2 , N ' 爸爸 ' , 1 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '3a55b881-b6c7-4682-8d59-639a77066227' , 3 , N ' ⼤姑 ' , 1 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'e27e364a-d28d-4607-b500-4522228775d6' , 4 , N ' ⼆姑 ' , 1 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '9a2b4580-cc1e-49a9-98c8-2c7ede13091a' , 5 , N ' 三姑 ' , 1 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '760e1af8-4962-464f-85bf-62ab817096ed' , 6 , N ' 姐姐 ' , 2 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'f31f3456-77ff-44ee-9b0e-be788828ef22' , 21 , N ' 姜彦 ' , 2 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '27868be6-b1fa-48c3-a8e9-8696fb65ef0a' , 8 , N ' 妹妹 ' , 2 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '5d10b662-a87f-45e0-ba83-dbce90d31d83' , 9 , N ' 周鑫 ' , 3 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'f835120a-2441-4b8e-a2ab-dd8f23accc2e' , 10 , N ' 慧丽 ' , 3 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'b13fcf04-e920-4256-bfa8-b670821fcf5e' , 11 , N ' 志浩 ' , 5 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'b917a081-9468-49c4-a27b-02e6685f9125' , 12 , N ' 夏慧 ' , 4 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '1f242652-dd75-4cba-8081-8c3b23e492d0' , 13 , N ' 腾腾 ' , 7 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'a1ca43a9-8742-44bd-ad7d-dcb595597c90' , 14 , N ' 睿聪 ' , 6 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'ae9e82aa-8a6a-4054-8320-28f7c95e707f' , 15 , N ' 凌睿 ' , 6 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'fa673193-46e0-4835-9763-29467537bddd' , 16 , N ' 蓬蓬 ' , 8 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'a7e3974b-e663-44f2-b3fa-f7f4dd797bd3' , 17 , N ' 周鑫⼉⼦ ' , 9 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '3656c2a4-06b1-4256-89f7-75b4c6dcf0fd' , 18 , N ' 慧丽⼉⼦ ' , 10 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '7d51726f-4ccc-45d9-88db-3c88124a94e7' , 19 , N ' 夏慧⼉⼦ ' , 12 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N '809794a5-0b51-4407-8740-6585d746f2a3' , 20 , N ' 志浩⼥⼉ ' , 11 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'aee1b0c4-a156-492b-92fb-453c96834e0c' , 22 , N ' 腾腾 ' , 21 ) INSERT
[dbo] . [C_TREEVIEW] ( [GUID] , [ID] , [NAME] , [PID] ) VALUES (N 'fbe4c4ed-170c-4229-b6e8-ba6a0fa280d3' , 23 , N ' 苏苏 ' , 21 )
/****** Script for SelectTopNRows command from SSMS ******/ SELECT
*
FROM
[WPF] . [dbo] . [C_TREEVIEW] UPDATE
[WPF] . [dbo] . [C_TREEVIEW]
SET PID =- 1 WHERE ID = 0 --1. 递归有效语句 1 with ts as
(
select ID,NAME,PID from
[WPF] . [dbo] . [C_TREEVIEW]
where ID = 2
and PID = 1
-- ⾸先要查询出最原始⽗级的信息
union
all
-- 全连接
select a.ID, a.NAME,a.PID from
[WPF] . [dbo] . [C_TREEVIEW] a inner
join ts b on a.PID = b.ID
-- 将整体的查询结果重新嵌套进语句中,将查询结果集中⼦类的 ID 与⽗类 ID 关联,进⾏查询 )
select
*
from ts order
by ID
--2. 递归有效语句 2 with subqry(ID,NAME,PID) as
(
select ID,NAME,PID from
[WPF] . [dbo] . [C_TREEVIEW]
where ID =
2
-- 指定 id
union
all
select
[WPF] . [dbo] . [C_TREEVIEW] .ID, [WPF] . [dbo] . [C_TREEVIEW] .NAME, [WPF] . [dbo] . [C_TREEVIEW] .PID from
[WPF] . [dbo] . [C_TREEVIEW] ,subqry
where
[WPF] . [dbo] . [C_TREEVIEW] .PID = subqry.ID ) select *
from subqry
--3. 递归有效语句 3 WITH Emp AS
(
SELECT
*
FROM
[WPF] . [dbo] . [C_TREEVIEW]
WHERE ID =
0
-- ⾸先要查询的⽗节点信息
UNION
ALL
-- 全连接
SELECT d. *
FROM Emp INNER
JOIN
[WPF] . [dbo] . [C_TREEVIEW] d ON d.PID = Emp.ID ) SELECT
*
FROM Emp OPTION (MAXRECURSION 0 ); -- OPTION (MAXRECURSION 0); 递归级数 0 表⽰⽆限级, 1: ⼀层; 2: 两层 ... --4. 递归有效语句 4 --https://so.m.sm.cn/c/www.360doc.cn/mip/373486751.html with my1 as (
select
*
from
[WPF] . [dbo] . [C_TREEVIEW]
where ID =
21
union
all
|
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |